package com.yups.dao;

import com.yups.model.Coach;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.SQLException;
import java.util.List;

/**
 * 教练数据处理
 *
 * @author 于鹏生
 * @date 2021/11/4 19:22
 */
public class CoachDao extends BaseDao {
	public boolean add(Coach coach) {
		String sql = "INSERT INTO `db_book`.`coach` ("
				+ " `company`, `photo`, `avatar`, `nickname`, `price`, "
				+ " `message`, `lat`, `lon` )"
				+ " VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ? )";
		try {
			return queryRunner.execute(sql,
					coach.getCompany(),
					coach.getPhoto(),
					coach.getAvatar(),
					coach.getNickname(),
					coach.getPrice(),
					coach.getMessage(),
					coach.getLat(),
					coach.getLon()
					) == 1;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	public boolean delete(int id) {
		try {
			String sql = "DELETE\n" +
					" FROM\n" +
					"  `db_book`.`coach`\n" +
					" WHERE `id` = ?";
			return queryRunner.execute(sql,id) == 1;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	public boolean update(Coach coach) {
		try {
			String sql = "UPDATE\n" +
					"  `db_book`.`coach`\n" +
					" SET\n" +
					"  `company` = ?,\n" +
					"  `photo` = ?,\n" +
					"  `avatar` = ?,\n" +
					"  `nickname` = ?,\n" +
					"  `price` = ?,\n" +
					"  `message` = ?,\n" +
					"  `lat` = ?,\n" +
					"  `lon` = ?\n" +
					" WHERE `id` = ?";
			return queryRunner.execute(sql,
					coach.getCompany(),
					coach.getPhoto(),
					coach.getAvatar(),
					coach.getNickname(),
					coach.getPrice(),
					coach.getMessage(),
					coach.getLat(),
					coach.getLon(),
					coach.getId()
			) == 1;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	public Coach get(int id) {
		String sql = "SELECT\n" +
				"  `id`,\n" +
				"  `company`,\n" +
				"  `photo`,\n" +
				"  `avatar`,\n" +
				"  `nickname`,\n" +
				"  `price`,\n" +
				"  `message`,\n" +
				"  `lat`,\n" +
				"  `lon`\n" +
				" FROM\n" +
				"  coach\n" +
				"  WHERE id = ?";
		try {
			return queryRunner.query(sql, new BeanHandler<>(Coach.class),id);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public List<Coach> getAll() {
		String sql = "SELECT `id`, `company`, `photo`, `avatar`, `nickname`, "
				+ "`price`, `message`, `lat`, `lon` "
				+" FROM `db_book`.`coach`";
		try {
			return queryRunner.query(sql, new BeanListHandler<>(Coach.class));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public Coach getCoachById(int id) {
		try {
			String sql = "select id, company, photo, avatar, nickname,"
					+ " price, message, distance"
					+ " from coach where id = ?";
			return queryRunner.query(sql, rs -> {
				Coach coach = null;
				if (rs.next()) {
					coach = new Coach(
							rs.getInt("id"),
							rs.getString("company"),
							rs.getString("photo"),
							rs.getString("avatar"),
							rs.getString("nickname"),
							rs.getDouble("price"),
							rs.getString("message"),
							rs.getDouble("lat"),
							rs.getDouble("lon")
					);
				}
				return coach;
			},id);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

}
